﻿using System;
using System.Collections.Generic;
using System.Data;
using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client;
using System.Web;
using System.Web.Caching;


using LMD.Constant;
using LMD.Lib.Oracle;
using LMD.Lib.DataClass;

namespace Cached
{
    /// <summary>
    /// Summary description for CompanyTable
    /// </summary>
    public class CompanyTable : CacheAbstraction
    {
//        public DataTable GetCompanyList()
//        {
//            return GetCompanyList(false);
//        }
//        public DataTable GetCompanyList(bool bForceRefresh)
//        {
//            String sxCacheKey = KCache.V_COMPANY;

//            if (bForceRefresh)
//            {
//                HttpRuntime.Cache.Remove(sxCacheKey);
//            }

//            if (HttpRuntime.Cache[sxCacheKey] == null)
//            {
//                DBLib dbl = new DBLib();

//                String sxQuery =
//                    @"
//                    select v.* 
//                    from 
//                        V_ICP_CUSTOMER v
//                    order by
//                        v.COMPANY_NAME asc";

//                DataTable dt = dbl.GetDataTable(sxQuery);

//                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
//            }
//            return (DataTable)HttpRuntime.Cache[sxCacheKey];
//        }

        String sxFilterHierarki_Sales =
            @"
            where v.SALES_EMPLOYEE_NO IN
            (
                select :prmUID from dual
                UNION ALL
                select EMPLOYEE_NO from slogix.p_slx_usergroup_member where p_slx_usergroup_id IN
                (
                    select 
                        p_slx_usergroup_id 
                    from 
                        slogix.p_slx_usergroup
                    start with 
                        p_slx_usergroup_id =
                        (
                            select max(a)
                            from 
                            (
                                select 
                                    decode(ishead, 'Y', p_slx_usergroup_id, -1) a
                                from 
                                    slogix.p_slx_usergroup_member 
                                where EMPLOYEE_NO = :prmUID
                            )
                        ) 
                    connect by prior 
                        p_slx_usergroup_id = parent_id
                )
            )";

        String sxFilterHierarki_CAR =
            @"
            where v.CAR_EMPLOYEE_NO IN
            (  
                select :prmUID from dual
                UNION ALL          
                select 
                    NIK 
                from 
                    v_icp_escalation
                start with 
                    NIK = :prmUID
                connect by prior 
                    t_qrtz_user_destination_id = parent_id
            )";

        public DataTable GetCompanyCompleteList()
        {
            return GetCompanyCompleteList(false);
        }
        public DataTable GetCompanyCompleteList(bool bForceRefresh)
        {
            String sxCacheKey = KCache.V_ICP_COMPANY_COMPLETE;

            if (ICPUser.RoleId == (int)ICPUserRole.CAR || ICPUser.RoleId == (int)ICPUserRole.CARAdminForum)
            {
                sxCacheKey += ICPUser.ID;
            }
            else if (ICPUser.RoleId == (int)ICPUserRole.Sales)
            {
                sxCacheKey += ICPUser.ID;
            }

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();

                String sxQuery =
                    @"
                    select v.* 
                    from 
                        V_ICP_CUSTOMER_COMPLETE v {0}
                    order by
                        v.COMPANY_NAME asc";

                List<OracleParameter> lsParams = new List<OracleParameter>();

                if (ICPUser.RoleId == (int)ICPUserRole.CAR || ICPUser.RoleId == (int)ICPUserRole.CARAdminForum)
                {
                    sxQuery = String.Format(sxQuery, sxFilterHierarki_CAR);

                    lsParams.Add(new OracleParameter());
                    lsParams[lsParams.Count - 1].ParameterName = "prmUID";
                    lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
                    lsParams[lsParams.Count - 1].Value = ICPUser.ID;
                }
                else if (ICPUser.RoleId == (int)ICPUserRole.Sales)
                {
                    sxQuery = String.Format(sxQuery, sxFilterHierarki_Sales);

                    lsParams.Add(new OracleParameter());
                    lsParams[lsParams.Count - 1].ParameterName = "prmUID";
                    lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
                    lsParams[lsParams.Count - 1].Value = ICPUser.ID;
                }
                else
                {
                    sxQuery = String.Format(sxQuery, String.Empty);
                }

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);

            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        public DataTable GetCompanyOfCarSales(String sxEmployeeNo)
        {
            return new DataView(
                GetCompanyCarSalesList(false, true),
                String.Format("CAR_EMPLOYEE_NO like '%{0}%' or SALES_EMPLOYEE_NO like '%{0}%'", sxEmployeeNo), 
                String.Empty, DataViewRowState.CurrentRows).ToTable();
        }

        public DataTable GetCompanyCarSalesList()
        {
            return GetCompanyCarSalesList(false, false);
        }
        public DataTable GetCompanyCarSalesList(bool bForceRefresh, bool bSelectAll)
        {
            String sxCacheKey = KCache.V_ICP_COMPANY_CAR_SALES;

            if (!bSelectAll)
            {
                if (ICPUser.RoleId == (int)ICPUserRole.CAR || ICPUser.RoleId == (int)ICPUserRole.CARAdminForum)
                {
                    sxCacheKey += ICPUser.ID;
                }
                else if (ICPUser.RoleId == (int)ICPUserRole.Sales)
                {
                    sxCacheKey += ICPUser.ID;
                }
            }

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();

                String sxQuery =
                    @"
                    select v.* 
                    from 
                        V_ICP_COMPANY_CAR_SALES v {0}
                    order by
                        v.COMPANY_NAME asc";

                List<OracleParameter> lsParams = new List<OracleParameter>();

                if (!bSelectAll && (ICPUser.RoleId == (int)ICPUserRole.CAR || ICPUser.RoleId == (int)ICPUserRole.CARAdminForum))
                {
                    sxQuery = String.Format(sxQuery, sxFilterHierarki_CAR);

                    lsParams.Add(new OracleParameter());
                    lsParams[lsParams.Count - 1].ParameterName = "prmUID";
                    lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
                    lsParams[lsParams.Count - 1].Value = ICPUser.ID;
                }
                else if (!bSelectAll && ICPUser.RoleId == (int)ICPUserRole.Sales)
                {
                    sxQuery = String.Format(sxQuery, sxFilterHierarki_Sales);

                    lsParams.Add(new OracleParameter());
                    lsParams[lsParams.Count - 1].ParameterName = "prmUID";
                    lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
                    lsParams[lsParams.Count - 1].Value = ICPUser.ID;
                }
                else
                {
                    sxQuery = String.Format(sxQuery, String.Empty);
                }

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);

            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }
		
		public DataTable GetCompanyCarSalesList2()
        {
            return GetCompanyCarSalesList2(false, false);
        }
        public DataTable GetCompanyCarSalesList2(bool bForceRefresh, bool bSelectAll)
        {
            String sxCacheKey = KCache.V_ICP_COMPANY_CAR_SALES;

            if (!bSelectAll)
            {
                if (ICPUser.RoleId == (int)ICPUserRole.CAR || ICPUser.RoleId == (int)ICPUserRole.CARAdminForum)
                {
                    sxCacheKey += ICPUser.ID;
                }
                else if (ICPUser.RoleId == (int)ICPUserRole.Sales)
                {
                    sxCacheKey += ICPUser.ID;
                }
            }

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();
                String sxQuery =
                    @"
                    select v.* 
                    from 
                        V_ICP_COMPANY_CAR_SALES v {0}
                    order by
                        v.COMPANY_NAME asc";
                DataTable dt;

                if (ICPUser.IsAdmin || ICPUser.IsAdminForum)
                {
					sxQuery = String.Format(sxQuery, String.Empty);
                    dt = dbl.GetDataTable(sxQuery);
                }
                else
                {
                    List<OracleParameter> lsParams = new List<OracleParameter>();

                    if (!bSelectAll && (ICPUser.RoleId == (int)ICPUserRole.CAR || ICPUser.RoleId == (int)ICPUserRole.CARAdminForum))
                    {
                        sxQuery = String.Format(sxQuery, sxFilterHierarki_CAR);

                        lsParams.Add(new OracleParameter());
                        lsParams[lsParams.Count - 1].ParameterName = "prmUID";
                        lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
                        lsParams[lsParams.Count - 1].Value = ICPUser.ID;
                    }
                    else if (!bSelectAll && ICPUser.RoleId == (int)ICPUserRole.Sales)
                    {
                        sxQuery = String.Format(sxQuery, sxFilterHierarki_Sales);

                        lsParams.Add(new OracleParameter());
                        lsParams[lsParams.Count - 1].ParameterName = "prmUID";
                        lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
                        lsParams[lsParams.Count - 1].Value = ICPUser.ID;
                    }
                    else
                    {
                        sxQuery = String.Format(sxQuery, String.Empty);
                    }
                    dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
                }
                
                AddToCache(sxCacheKey, dt, CacheType.Daily);

            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }
        public DataTable GetCompanyList_NameStartsWith(String sxFilter)
        {
            return GetCompanyList_NameStartsWith(sxFilter, false);
        }
        public DataTable GetCompanyList_NameStartsWith(String sxFilter, bool bForceRefresh)
        {
            return new DataView(
                GetCompanyCarSalesList(bForceRefresh, false),
                String.Format("COMPANY_NAME like '{0}%'", sxFilter),
                "COMPANY_NAME asc",
                DataViewRowState.CurrentRows).ToTable();
        }

        public DataTable GetCompanyList_Categorized(String sxFilter, String sxColumn)
        {
            return GetCompanyList_Categorized(sxFilter, false, sxColumn);
        }
        public DataTable GetCompanyList_Categorized(String sxFilter, bool bForceRefresh, String sxColumn)
        {
            if (String.IsNullOrEmpty(sxFilter))
            {
                return GetCompanyCarSalesList(bForceRefresh, false); 
            }
            else
            {
                return new DataView(
                    GetCompanyCompleteList(bForceRefresh),
                    String.Format("{0} = '{1}'", sxColumn, sxFilter),
                    "COMPANY_NAME asc",
                    DataViewRowState.CurrentRows).ToTable();
            }
        }

        public DataTable GetCompanyList_ByCar(String sxCARId)
        {
            return GetCompanyList_ByCar(sxCARId,false);
        }
        public DataTable GetCompanyList_ByCar(String sxCARId, bool bForceRefresh)
        {
            String sxCacheKey = KCache.V_COMPANY_BY_CAR + sxCARId;

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();

                OracleParameter prmID = new OracleParameter();
                prmID.ParameterName = "prmID";
                prmID.OracleDbType = OracleDbType.VarChar;
                prmID.Value = sxCARId;

                String sxQuery =
                    @"
                    select
                        v1.*
                    from
                        V_ICP_COMPANY_CAR_SALES v1
                    where
                        v1.CAR_EMPLOYEE_NO IN
                        (
							select :prmID from dual
                            UNION ALL
                            SELECT DISTINCT NIK
                            FROM 
                            v_icp_escalation
                            START WITH NIK = :prmID        
                            CONNECT BY PRIOR t_qrtz_user_destination_id = parent_id
                        )
                    order by v1.COMPANY_NAME asc";

                DataTable dt = dbl.GetDataTable(sxQuery, prmID);

                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        public CompanyProfileData GetCompanyProfileData(int iCompanyId)
        {
            return GetCompanyProfileData(iCompanyId, false);
        }
        public CompanyProfileData GetCompanyProfileData(int iCompanyId, bool bForceRefresh)
        {
            DataView dv = new DataView(
                GetCompanyCompleteList(),
                String.Format("CUSTOMER_ID = '{0}'", iCompanyId.ToString()),
                String.Empty,
                DataViewRowState.CurrentRows);

            CompanyProfileData cpd = new CompanyProfileData();
            cpd.ID = iCompanyId;

            if (dv.Count <= 0)
                return cpd;

            cpd.sxCompanyName = dv[0]["COMPANY_NAME"].ToString();
            cpd.sxBusinessSegment = dv[0]["P_BUSINESS_SEGMENT_CODE"].ToString();
            cpd.sxCompanySegment = dv[0]["P_COMPANY_SEGMENT_CODE"].ToString();
            cpd.sxClass = dv[0]["P_CUSTOMER_CLASS_CODE"].ToString();
            cpd.sxNPWP = dv[0]["NPWP"].ToString();
            cpd.sxFullAddress = String.Format("{1}{2}{0}{3}{4}{5}{6}{0}{7}{8}",
                "<br/>&nbsp;&nbsp;",
                dv[0]["P_BUILDING_CODE"].ToString().AddSpacesIfNotEmpty(),
                dv[0]["FLOOR_NO"].ToString().AddSpacesIfNotEmpty(),
                dv[0]["P_PREFIX_ADDRESS_CODE"].ToString().AddSpacesIfNotEmpty(),
                dv[0]["ADDRESS_NAME"].ToString().AddSpacesIfNotEmpty(),
                dv[0]["ADDRESS_NO"].ToString().AddSpacesIfNotEmpty(),
                dv[0]["P_ADD_INFO_REGION_CODE"].ToString().AddSpacesIfNotEmpty(),
                dv[0]["REGION_NAME"].ToString().AddSpacesIfNotEmpty(),
                dv[0]["ZIP_CODE"].ToString().AddSpacesIfNotEmpty());

            DataTable dtInfos = GetCompanyInfoList(iCompanyId, bForceRefresh);

            foreach (DataRow dr in dtInfos.Rows)
            {
                String sxValue = Convert.ToString(dr["VALUE"]);
                String sxKey = Convert.ToString(dr["P_CUSTOMER_INFO_TYPE_ID"]);
                cpd.AddCustomData(sxKey, sxValue);

                if (dr["SUMMARY_ID"] != DBNull.Value)
                {
                    String sxText = Convert.ToString(dr["SUMMARY_TEXT"]);
                    try
                    {
                        cpd.sxSummary += String.Format(sxText, sxValue);
                    }
                    catch
                    {
                        cpd.sxSummary += sxText + " " + sxValue;
                    }

                    cpd.sxSummary += ". ";
                }
            }

            return cpd;
        }

        public DataTable GetCompanyInfoList(int iCompanyId)
        {
            return GetCompanyInfoList(iCompanyId,false);
        }
        public DataTable GetCompanyInfoList(int iCompanyId, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}_{1}", KCache.V_COMPANY_INFO_LIST, iCompanyId.ToString());

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();

                OracleParameter param = new OracleParameter();
                param.ParameterName = "custId";
                param.OracleDbType = OracleDbType.Number;
                param.Value = iCompanyId;

                String sxQuery =
                    @"
                    select 
                        t.T_CUSTOMER_INFO_ID ID, 
                        t.CUSTOMER_ID, 
                        t.VALUE, 
                        t.P_CUSTOMER_INFO_TYPE_ID, 
                        p1.CODE INFO_TYPE,
                        p1.P_INPUT_CONTROL_TYPE_ID CONTROL_TYPE_ID,
                        p2.P_CUSTOMER_INFO_SUMMARY_ID SUMMARY_ID,
                        p2.TEXT SUMMARY_TEXT
                    from 
                        T_CUSTOMER_INFO t
                        left join P_CUSTOMER_INFO_TYPE p1
                            on t.P_CUSTOMER_INFO_TYPE_ID = p1.P_CUSTOMER_INFO_TYPE_ID
                        left join P_CUSTOMER_INFO_SUMMARY p2
                            on p2.P_CUSTOMER_INFO_TYPE_ID = p1.P_CUSTOMER_INFO_TYPE_ID
                    where
                        t.CUSTOMER_ID = :custId
                        and nvl(p1.VALID_TO, Sysdate+1) > Sysdate
                        and nvl(p2.VALID_TO, Sysdate+1) > Sysdate
                    order by
                        p1.CODE ASC";

                DataTable dt = dbl.GetDataTable(sxQuery,param);
                AddToCache(sxCacheKey, dt, CacheType.FiveMinutes);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        public DataTable GetCompanyBriefDesc(int iCompanyId)
        {
            return GetCompanyBriefDesc(iCompanyId, false);
        }
        public DataTable GetCompanyBriefDesc(int iCompanyId, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}_{1}", KCache.V_COMPANY_BRIEF_DESC, iCompanyId.ToString());

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();

                OracleParameter param = new OracleParameter();
                param.ParameterName = "custId";
                param.OracleDbType = OracleDbType.Number;
                param.Value = iCompanyId;

                String sxQuery =
                    @"
                    select 
                        T_CUSTOMER_BRIEF_DESC_ID ID, 
                        VALUE
                    from 
                        T_CUSTOMER_BRIEF_DESC
                    where
                        CUSTOMER_ID = :custId
                        and IS_ACTIVE = 'Y'";

                DataTable dt = dbl.GetDataTable(sxQuery, param);

                AddToCache(sxCacheKey, dt, CacheType.FiveMinutes);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        public DataTable GetCompanyBriefDescList(int iCompanyId)
        {
            return GetCompanyBriefDescList(iCompanyId, false);
        }
        public DataTable GetCompanyBriefDescList(int iCompanyId, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}_{1}", KCache.V_COMPANY_BRIEF_LIST, iCompanyId.ToString());

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();

                OracleParameter param = new OracleParameter();
                param.ParameterName = "custId";
                param.OracleDbType = OracleDbType.Number;
                param.Value = iCompanyId;

                String sxQuery =
                    @"
                    select 
                        T_CUSTOMER_BRIEF_DESC_ID ID, 
                        VALUE,
                        CREATE_DATE,
                        CREATE_BY,
                        IS_ACTIVE
                    from 
                        T_CUSTOMER_BRIEF_DESC_ID
                    where
                        CUSTOMER_ID = :custId
                    order by
                        CREATE_DATE desc";

                DataTable dt = dbl.GetDataTable(sxQuery, param);

                AddToCache(sxCacheKey, dt, CacheType.FiveMinutes);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        public override String GetCacheFilename()
        {
            return KCache.Filename.COMPANY_RELATED;
        }
    }
}